package com.sanqing.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.sanqing.bean.Message;
import com.sanqing.dao.MessageDAO;
import com.sanqing.util.DBConnection;
import com.sanqing.util.Page;

public class MessageDAOImpl implements MessageDAO{

	/*
	 * (non-Javadoc)
	 * @see com.sanqing.dao.MessageDAO#addMessage(com.sanqing.bean.Message)
	 * 此方法用于添加发布消息信息
	 */
	@Override
	public void addMessage(Message message) {
		// TODO Auto-generated method stub
		
		Connection conn=DBConnection.getConnection();
		String addSQL="insert into tb_message(messageTitle,messageContent,employeeID,publishTime) values (?,?,?,?)";
		PreparedStatement pstmt=null;
		try{
			
			pstmt=conn.prepareStatement(addSQL);
			pstmt.setString(1, message.getMessageTitle());
			pstmt.setString(2, message.getMessageContent());
			pstmt.setInt(3, message.getEmployeeID());
			pstmt.setTimestamp(4, new Timestamp(message.getPublishTime().getTime()));
			pstmt.executeUpdate();
			
		}catch(SQLException e)
		{
			e.printStackTrace();
		}finally{
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
		
	}

	/*
	 * (non-Javadoc)
	 * @see com.sanqing.dao.MessageDAO#updateMessage(com.sanqing.bean.Message)
	 * 此方法用于更新发布的消息
	 */
	@Override
	public void updateMessage(Message message) {
		// TODO Auto-generated method stub
		Connection conn=DBConnection.getConnection();
		String updateSQL="update tb_message set messageTitle=?,messageContent=? where messageID=?";
		PreparedStatement pstmt=null;
		
       try{
			
			pstmt=conn.prepareStatement(updateSQL);
			pstmt.setString(1, message.getMessageTitle());
			pstmt.setString(2, message.getMessageContent());
			pstmt.setInt(3, message.getMessageID());
			pstmt.executeUpdate();
			
		}catch(SQLException e)
		{
			e.printStackTrace();
		}finally{
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
		
	}

	/*
	 * (non-Javadoc)
	 * @see com.sanqing.dao.MessageDAO#deleteMessage(int)
	 * 此方法用于删除指定消息ID的消息
	 */
	@Override
	public void deleteMessage(int messageID) {
		// TODO Auto-generated method stub
		Connection conn=DBConnection.getConnection();
		String deleteSQL=" delete from tb_message where messageID=?";
		
		PreparedStatement pstmt=null;
		
		
		try{
			
			pstmt=conn.prepareStatement(deleteSQL);
			pstmt.setInt(1, messageID);
			pstmt.executeUpdate();
			
			}
		catch(SQLException e)
		{
			e.printStackTrace();
		}finally{
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
	}

	/*
	 * (non-Javadoc)
	 * @see com.sanqing.dao.MessageDAO#findAllMessage(com.sanqing.util.Page)
	 * 此方法返回全部消息列表
	 */
	@Override
	public List<Message> findAllMessage(Page page) {
		// TODO Auto-generated method stub
		Connection conn=DBConnection.getConnection();
		String findSQL="select * from tb_message order by publishTime desc limit ?,?";
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		List<Message>messages = new ArrayList<Message>();
		
		try{
			
			pstmt = conn.prepareStatement(findSQL);
			pstmt.setInt(1, page.getBeginIndex());
			pstmt.setInt(2, page.getEveryPage());
			rs=pstmt.executeQuery();
			
			//System.out.print(rs.next());
			
			
			while(rs.next()){
				//System.out.print("执行到这里2");
				Message message=new Message();
				message.setMessageID(rs.getInt(1));
				message.setMessageTitle(rs.getString(2));
				message.setMessageContent(rs.getString(3));
				message.setEmployeeID(rs.getInt(4));
				message.setPublishTime(rs.getTimestamp(5));
				
				messages.add(message);
			}
		}catch(SQLException e)
		{
			e.printStackTrace();
		}finally{
			DBConnection.close(rs);
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
		return messages;
	}

	/*
	 * (non-Javadoc)
	 * @see com.sanqing.dao.MessageDAO#findMessageById(int)
	 * 此方法用于查找指定消息id的消息信息
	 */
	@Override
	public Message findMessageById(int messageID) {
		// TODO Auto-generated method stub
		Connection conn=DBConnection.getConnection();
		String querySQL="select * from tb_message where messageID=?";
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		Message message=null;
		try{
			pstmt=conn.prepareStatement(querySQL);
			pstmt.setInt(1, messageID);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				message=new Message();
				message.setMessageID(rs.getInt(1));
				message.setMessageTitle(rs.getString(2));
				message.setMessageContent(rs.getString(3));
				message.setEmployeeID(rs.getInt(4));
				message.setPublishTime(rs.getTimestamp(5));
				
			}
		}catch(SQLException e)
		{
			e.printStackTrace();
		}finally{
			DBConnection.close(rs);
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
		
		return message;
	}

	/*
	 * (non-Javadoc)
	 * @see com.sanqing.dao.MessageDAO#findAllCount()
	 * 此方法用于统计消息条数
	 */
	@Override
	public int findAllCount() {
		// TODO Auto-generated method stub
		Connection conn=DBConnection.getConnection();
		String findSQL="select count(*) from tb_message";
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		int count=0;
		try{
			pstmt=conn.prepareStatement(findSQL);
			rs=pstmt.executeQuery();
			if(rs.next()){
				count=rs.getInt(1);
				//System.out.print(count);
			}
		}catch(SQLException e)
		{
			e.printStackTrace();
		}finally{
			DBConnection.close(rs);
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
		return count;
	}
	
}
